﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using webapp4U;
using System.IO;


public partial class UserControl_ControlNhapSoLieuTuNPPNMD : System.Web.UI.UserControl
{
    string strconn = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
    ClassExecute.ClassExecute dt;
    string strSiteCodeDistributor = "FM_00000002"; //Distributor NMD
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //ViewState["flag"] = false;
        }
        lblFileName.Text = txtFileName.Value;
    }

    void ShowMessageBox(string text)
    {
        lblMessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
        
    }
    void CloseMessageBox()
    {
        MessageBox.ShowOnPageLoad = false;

    }


    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (Session["sitecode"] != null && txtFileName.Value != "")
        {
            webapp4U.CheckFile chkFile = new webapp4U.CheckFile();
            dt = new ClassExecute.ClassExecute();
            DataTable dtFileNameCopy = dt.Get_Data_DataTable(strconn, ref error, "TTr_UploadFile_CreateFileNameCopyDateNow", CommandType.StoredProcedure,
                new SqlParameter("@FileNameOrginaly", txtFileName.Value),
                new SqlParameter("@SiteCode", strSiteCodeDistributor));
            string fileNameDestination = webapp4U.Common.GetFileName() + "_" + txtFileName.Value;            
            
            if (dtFileNameCopy.Rows.Count > 0)
            {
                fileNameDestination = fileNameDestination.Insert(fileNameDestination.LastIndexOf('.'), dtFileNameCopy.Rows[0][0].ToString());

            }

            string YyyyMm = DateTime.Now.ToString("yyyyMM");
            string pathFolder = @"UserUpload\ImportedFileExcel\" + YyyyMm;
            string savepath = "";
            savepath = webapp4U.Globals.PhysicalApplicationPath() + pathFolder;

            if (!Directory.Exists(savepath))
                Directory.CreateDirectory(savepath);
            string physicalPathFolder = @"/UserUpload/ImportedFileExcel/" + YyyyMm + "/";
            //Rename and Move fiel to  ImportedFileExcel/
            string filePathSource = webapp4U.Globals.PhysicalApplicationPath() + @"/UserUpload/UploadTempFileExcel/" + txtFileName.Value;
            string filePathDes = webapp4U.Globals.PhysicalApplicationPath() + physicalPathFolder + fileNameDestination;

            File.Move(filePathSource, filePathDes);

            if (dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_UploadFile_Insert", ref error,
                new SqlParameter("@SiteCode", strSiteCodeDistributor),
                new SqlParameter("@FileNameOrginaly", txtFileName.Value),
                new SqlParameter("@FileNameDestination", fileNameDestination),
                new SqlParameter("@PathFolder", pathFolder),
                new SqlParameter("@UserUpload", Session["user"].ToString()),
                new SqlParameter("@Title", txtTitle.Text)))
            {

                Excel2Sql(physicalPathFolder, fileNameDestination);
                dt = new ClassExecute.ClassExecute();
                DataTable dtNewCust = dt.Get_Data_DataTable(strconn, ref error, "TTr_InsertDataImportDKSHDemo", CommandType.StoredProcedure);                

                if (dtNewCust != null)
                {
                    txtFileName.Value = "";
                    lblFileName.Text = "";
                    //Move file 
                    lblMsg.Text = "Đã cập nhật số liệu thành công";
                    lblMsg.Visible = true;
                    gviewList.DataSource = null;
                    gviewList.DataBind();

                }
                else
                {
                    lblFileName.Text = "";
                    lblMsg.Text = "Cập nhật số liệu thất bại";
                    lblMsg.Visible = true;
                }
            }

        }
        else
        {
            Response.Redirect(webapp4U.Globals.ApplicationPath + "Default.aspx");
            //
            //lblMsg.Text = "Không có file";
            //lblMsg.Visible = true;
        }
    }

    protected void Excel2Sql(string PhysicalPathFolder, string fileName)
    {
        string strSource = Globals.PhysicalApplicationPath() + PhysicalPathFolder + fileName;
        //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False";

        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False");

        try
        {
            dt = new ClassExecute.ClassExecute();
            dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_TempImportNMD_Delete", ref error);            
            OleDbCommand ocmd = new OleDbCommand("select * from [NMD$] ", oconn);
            oconn.Open();
            OleDbDataReader odr = ocmd.ExecuteReader();

            string strMSVT = "";
            string strTENVT = "";
            string strQUYCACH = "";
            string strSOLO = "";
            string strHANDUNG = "";
            string strMAKH = "";
            string strTENKH = "";
            string strTENKH1 = "";
            string strDC = "";
            string strQHT = "";
            string strMAQUAN = "";
            string strCTYPE = "";
            string strCHANNEL = "";
            string strLAND = "";
            string strDISTRICT = "";
            string strPHIEU = "";
            string strHOADON = "";
            string strNGAY = "";
            string strTDV = "";
            string strQTY = "";
            string strDGLE = "";
            string strTT = "";
            string strTTV = "";
            string strNGUYENGIA = "";
            string strMONTH = "";
            string strDISC = "";
            
            int STT = 0;
            
            while (odr.Read())
            {
                STT = STT + 1;
                strMSVT = Valid(odr, 0);
                strTENVT = Valid(odr, 1);
                strQUYCACH = Valid(odr, 2);
                strSOLO = Valid(odr, 3);
                strHANDUNG = Valid(odr, 4);
                strMAKH = Valid(odr, 5);
                strTENKH = Valid(odr, 6);
                strTENKH1 = Valid(odr, 7);
                strDC = Valid(odr, 8);
                strQHT = Valid(odr, 9);
                strMAQUAN = Valid(odr, 10);
                strCTYPE = Valid(odr, 11);
                strCHANNEL = Valid(odr, 12);
                strLAND = Valid(odr, 13);
                strDISTRICT = Valid(odr, 14);
                strPHIEU = Valid(odr, 15);
                strHOADON = Valid(odr, 16);
                strNGAY = Valid(odr, 17);
                strTDV = Valid(odr, 18);
                
                strQTY = Valid(odr, 19);
                strQTY = strQTY.Replace(',', '.');

                strDGLE = Valid(odr, 20);
                strDGLE = strDGLE.Replace(',', '.');

                strTT = Valid(odr, 21);
                strTT = strTT.Replace(',', '.');

                strTTV = Valid(odr, 22);
                strTTV = strTTV.Replace(',', '.');

                strNGUYENGIA = Valid(odr, 23);
                strNGUYENGIA = strNGUYENGIA.Replace(',', '.');

                strMONTH = Valid(odr, 24);
                strDISC = Valid(odr, 25);

                if (strMSVT != "" || strTENVT != "" || strQUYCACH != "" || strSOLO != "" || strHANDUNG != "" ||
                    strMAKH != "" || strTENKH != "" || strTENKH1 != "" || strDC != "" || strQHT != "" ||
                    strMAQUAN != "" || strCTYPE != "" || strCHANNEL != "" || strLAND != "" || strDISTRICT != "" ||
                    strPHIEU != "" || strHOADON != "" || strNGAY != "" || strTDV != "" || strQTY != "" ||
                    strDGLE != "" || strTT != "" || strTTV != "" || strNGUYENGIA != "" || strMONTH != "" || strDISC != "")
                {
                    dt.ExecuteSQl_bool(strconn, CommandType.StoredProcedure, "TTr_TempImportNMD_Insert", ref error,
                        new SqlParameter("@STT", STT),
                        new SqlParameter("@MSVT", strMSVT), new SqlParameter("@TENVT", strTENVT),
                        new SqlParameter("@QUYCACH", strQUYCACH), new SqlParameter("@SOLO", strSOLO),
                        new SqlParameter("@HANDUNG", strHANDUNG), new SqlParameter("@MAKH", strMAKH),
                        new SqlParameter("@TENKH", strTENKH), new SqlParameter("@TENKH1", strTENKH1),
                        new SqlParameter("@DC", strDC), new SqlParameter("@QHT", strQHT),
                        new SqlParameter("@MAQUAN", strMAQUAN), new SqlParameter("@CTYPE", strCTYPE),
                        new SqlParameter("@CHANNEL", strCHANNEL), new SqlParameter("@LAND", strLAND),
                        new SqlParameter("@DISTRICT", strDISTRICT), new SqlParameter("@PHIEU", strPHIEU),
                        new SqlParameter("@HOADON", strHOADON), new SqlParameter("@NGAY", strNGAY),
                        new SqlParameter("@TDV", strTDV), new SqlParameter("@QTY", strQTY),
                        new SqlParameter("@DGLE", strDGLE), new SqlParameter("@TT", strTT),
                        new SqlParameter("@TTV", strTTV), new SqlParameter("@NGUYENGIA", strNGUYENGIA),
                        new SqlParameter("@MONTH", strMONTH), new SqlParameter("@DISC", strDISC),
                        new SqlParameter("@SiteCode", strSiteCodeDistributor), new SqlParameter("@FileName", fileName));
                }
                else
                {
                    break;
                }
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblMsg.Text = "File không đúng định dạng của NPP";
            lblMsg.Visible = true;
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ee.Message, method);

        }

    }
    
    
    protected string Valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by ""
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString("");
    }

    protected string Valid(OleDbDataReader myreader, string columnName)//if any columns are found null then they are replaced by ""
    {
        object val = myreader[columnName];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString("");
    }

    protected void btnView_Click(object sender, EventArgs e)
    {
        if (Session["sitecode"] != null && txtFileName.Value != "")
        {
            try
            {
                Excel2Sql(@"/UserUpload/UploadTempFileExcel/", txtFileName.Value);
                dt = new ClassExecute.ClassExecute();
                DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportNMD_GetAll", CommandType.StoredProcedure);
                if (db.Rows.Count > 0)
                {
                    gviewList.DataSource = db;
                    gviewList.DataBind();
                    lblMsg.Text = "";
                    lblMsg.Visible = true;
                }
                else
                {
                    lblMsg.Text = "Chưa có số liệu.";
                    lblMsg.Visible = true;
                }
            }
            catch (Exception ex)
            {
                lblMsg.Text = "File không đúng định dạng của NPP";
                lblMsg.Visible = true;
                return;
            }
        }
        else
        {
            Response.Redirect(webapp4U.Globals.ApplicationPath + "Default.aspx");
            //Login lại
            //lblMsg.Text = "Không có file";
            //lblMsg.Visible = true;
        }
    }
        

    protected void gviewList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gviewList.PageIndex = e.NewPageIndex;
        dt = new ClassExecute.ClassExecute();
        DataTable db = dt.Get_Data_DataTable(strconn, ref error, "TTr_TempImportNMD_GetAll", CommandType.StoredProcedure);
        if (db.Rows.Count > 0)
        {
            gviewList.DataSource = db;
            gviewList.DataBind();
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (txtFileName.Value != "")
        {
            FileProcess.DeleteFile(Globals.PhysicalApplicationPath() + "/UserUpload/UploadTempFileExcel/" + txtFileName.Value);
            txtFileName.Value = "";
            lblFileName.Text = "";
            gviewList.DataSource = null;
            gviewList.DataBind();
        }
    }
}
